{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9d679d4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_SparkSQL_讯捷集团组合销售分析.ipynb\n",
    "# Create By GF 2023-11-27 16:54"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1f7720a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.types import IntegerType, DateType\n",
    "from pyspark.sql.window import Window\n",
    "from pyspark.sql.functions import col, desc, format_string, lit, row_number, count, when"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d2d72477-97b3-45be-85f0-9dfb577714e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PySpark 创建 Spark 会话(连接)。\n",
    "# --------------------------------------------------\n",
    "spark = SparkSession.builder.appName('Basic').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "69332d15-b527-4daa-bccf-c8ba9d39f864",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.option(\"header\",\"true\").option(\"encoding\", \"gbk\").csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_销售数据_2023年08月-2023年10月.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9b118e3d-155f-423f-8b59-093d1834cb8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['订单号', '日期', '仓库名称', '门店类别', '仓库简称', '所属区域', '产品名称', '总类', '单价', '数量', '金额', '成本', '毛利', '预估毛利', '运营代收', '考核毛利', '毛利率', '仓库类别', '体系', '部门', '同比', '商圈', '导购']\n"
     ]
    }
   ],
   "source": [
    "# 字段列表\n",
    "# --------------------------------------------------\n",
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "a57880d8-e1ef-418e-a2b0-44ca7f7420b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 类型转换\n",
    "ChangeType = df\n",
    "# --------------------------------------------------\n",
    "ChangeType = ChangeType.withColumn(\"日期\",     df[\"日期\"].cast(DateType()))\n",
    "ChangeType = ChangeType.withColumn(\"订单号\",   df[\"订单号\"].cast(IntegerType()))\n",
    "ChangeType = ChangeType.withColumn(\"考核毛利\", df[\"考核毛利\"].cast(\"double\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f9de2f48-a946-4bc2-9282-8a18fd709551",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- 订单号: integer (nullable = true)\n",
      " |-- 日期: date (nullable = true)\n",
      " |-- 仓库名称: string (nullable = true)\n",
      " |-- 门店类别: string (nullable = true)\n",
      " |-- 仓库简称: string (nullable = true)\n",
      " |-- 所属区域: string (nullable = true)\n",
      " |-- 产品名称: string (nullable = true)\n",
      " |-- 总类: string (nullable = true)\n",
      " |-- 单价: string (nullable = true)\n",
      " |-- 数量: string (nullable = true)\n",
      " |-- 金额: string (nullable = true)\n",
      " |-- 成本: string (nullable = true)\n",
      " |-- 毛利: string (nullable = true)\n",
      " |-- 预估毛利: string (nullable = true)\n",
      " |-- 运营代收: string (nullable = true)\n",
      " |-- 考核毛利: double (nullable = true)\n",
      " |-- 毛利率: string (nullable = true)\n",
      " |-- 仓库类别: string (nullable = true)\n",
      " |-- 体系: string (nullable = true)\n",
      " |-- 部门: string (nullable = true)\n",
      " |-- 同比: string (nullable = true)\n",
      " |-- 商圈: string (nullable = true)\n",
      " |-- 导购: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# PySpark 打印 DataFrame 的每个字段(列)的类型。\n",
    "# --------------------------------------------------\n",
    "ChangeType.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3f53a0d0-5d78-4cd2-8ee3-24ad64f64c28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+----+----+-----------------------+----+----+----+--------+-------------+\n",
      "|  订单号|      日期|体系|部门|               产品名称|单价|数量|金额|考核毛利|         导购|\n",
      "+--------+----------+----+----+-----------------------+----+----+----+--------+-------------+\n",
      "|22338496|2023-08-01|华为|西安| 荣耀90-AN00(16G+512...|   0|   1|2881|  451.86|何养婷(34943)|\n",
      "|22338513|2023-08-01|形象|贵州|               立本回收|   0|   1|   0|     0.0|杨冬旭(73217)|\n",
      "|22338536|2023-08-01|形象|四部|   苹果iphone13(256G)粉|   0|   1|5499|   148.5|吴小渝(13201)|\n",
      "|22338550|2023-08-01|形象|四部|    Apple系统终身服务卡|   0|   1|1288|  1287.2|曾成富(65559)|\n",
      "|22338550|2023-08-01|形象|四部|            两年碎屏PRO|   0|   1| 228|    38.0|曾成富(65559)|\n",
      "|22338550|2023-08-01|形象|四部|               验机服务|   0|   1|   0|     0.0|曾成富(65559)|\n",
      "|22339157|2023-08-01|形象|西安|               技术服务|   0|   1| 337|   337.0|董运超(06996)|\n",
      "|22339158|2023-08-01|形象|一部|               保障服务|   0|   0| 203|   203.0|王乙岚(9172X)|\n",
      "|22339159|2023-08-01|形象|一部|iKeloah镜头膜iP14Pro...|   0|   1| 136|   120.6|  梁超(00714)|\n",
      "|22339170|2023-08-01|形象|云南|   Mophie PD 30W USB...|   0|   1| 158|  113.96|晏德芬(51728)|\n",
      "|22339190|2023-08-01|形象|二部|  iKeloah膜iP14ProMa...|   0|   1| 190|   160.5|  胡旭(77228)|\n",
      "|22339209|2023-08-01|形象|西安|             活动优惠券|   0|   0|-100|  -100.0|  赵鑫(06412)|\n",
      "|22339225|2023-08-01|形象|三部|               保障服务|   0|   0| 446|   446.0|欧榕海(8427X)|\n",
      "|22339259|2023-08-01|形象|重庆|         达尔文保障计划|   0|   1| 378|    63.0|周玉莲(87188)|\n",
      "|22339398|2023-08-01|形象|贵州|               保障服务|   0|   0|1154|  1154.0|  罗丹(90029)|\n",
      "|22339443|2023-08-01|形象|西安|   USB-C to Lightnin...|   0|   1| 145|   107.0|董运超(06996)|\n",
      "|22339468|2023-08-01|形象|贵州|  RideAir膜iP14ProMa...|   0|   1| 240|   207.0|  云莉(14022)|\n",
      "|22339468|2023-08-01|形象|贵州| 碎屏保障服务两年期PG-L|   0|   1| 126|    21.0|  云莉(14022)|\n",
      "|22339501|2023-08-01|华为|贵州|   真空保温杯（CX礼品）|   0|  -1|   0|     0.0|张筱沐(18023)|\n",
      "|22339510|2023-08-01|形象|重庆|   Mophie PD 30W USB...|   0|   1| 168|   123.8|  叶鹏(28916)|\n",
      "+--------+----------+----+----+-----------------------+----+----+----+--------+-------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 预览数据\n",
    "Preview = ChangeType\n",
    "# --------------------------------------------------\n",
    "Preview.select([\"订单号\", \"日期\", \"体系\", \"部门\", \"产品名称\", \"单价\", \"数量\", \"金额\", \"考核毛利\", \"导购\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "393dadf2-b524-4075-992a-70d9703182b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+------------------------------+--------+--------------+------------+----------------+\n",
      "|  订单号|      日期|                      产品名称|考核毛利|OLAP订单号计数|组合标记(简)|    组合标记(细)|\n",
      "+--------+----------+------------------------------+--------+--------------+------------+----------------+\n",
      "|22338610|2023-08-01|          USB-C to Lightnin...|   106.5|             1|      非组合|非组合(22338610)|\n",
      "|22338929|2023-08-01|          iKeloah USB-C to ...|  139.52|             2|        组合|  组合(22338929)|\n",
      "|22338929|2023-08-01|          Mophie PD 30W USB...|  253.96|             2|        组合|  组合(22338929)|\n",
      "|22340298|2023-08-01|                      立本回收|     0.0|             1|      非组合|非组合(22340298)|\n",
      "|22340843|2023-08-01|          苹果iphone14(128G)紫|  261.66|             5|        组合|  组合(22340843)|\n",
      "|22340843|2023-08-01|     RideAir膜iP14康宁盔甲W...|   172.0|             5|        组合|  组合(22340843)|\n",
      "|22340843|2023-08-01|          Mophie PD 30W USB...|   123.8|             5|        组合|  组合(22340843)|\n",
      "|22340843|2023-08-01|                      验机服务|   486.0|             5|        组合|  组合(22340843)|\n",
      "|22340843|2023-08-01|    TredPro苹果手机壳iP14机...|   162.5|             5|        组合|  组合(22340843)|\n",
      "|22341078|2023-08-01|          Mophie PD 30W USB...|  255.01|             2|        组合|  组合(22341078)|\n",
      "|22341078|2023-08-01|Nuguma 透明超级快充移动电源...|  174.65|             2|        组合|  组合(22341078)|\n",
      "|22341419|2023-08-01|     RideAir苹果手机壳iP14P...|  262.84|             5|        组合|  组合(22341419)|\n",
      "|22341419|2023-08-01|                      技术服务|   156.0|             5|        组合|  组合(22341419)|\n",
      "|22341419|2023-08-01|        苹果iphone14pro(256...|  201.67|             5|        组合|  组合(22341419)|\n",
      "|22341419|2023-08-01|     苹果原装触控笔二代(A2051)|   123.0|             5|        组合|  组合(22341419)|\n",
      "|22341419|2023-08-01|        苹果(M2)iPad-pro11....|   312.5|             5|        组合|  组合(22341419)|\n",
      "|22342194|2023-08-01|      苹果纪念保温杯（CX礼品）|     0.0|            10|        组合|  组合(22342194)|\n",
      "|22342194|2023-08-01|       iKeloah镜头膜iP14Pro...|   121.4|            10|        组合|  组合(22342194)|\n",
      "|22342194|2023-08-01|                      技术服务|   617.0|            10|        组合|  组合(22342194)|\n",
      "|22342194|2023-08-01|        苹果iphone14promax(...|  303.69|            10|        组合|  组合(22342194)|\n",
      "+--------+----------+------------------------------+--------+--------------+------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 标记字段: 套餐标记\n",
    "Calc_Pack_Mark = ChangeType\n",
    "# --------------------------------------------------\n",
    "Calc_Pack_Mark = Calc_Pack_Mark.withColumn(\"OLAP订单号计数\", count(\"订单号\").over(Window.partitionBy(\"订单号\")))\n",
    "# --------------------------------------------------\n",
    "# 使用 withColumn 方法来创建一个新的 DataFrame, 并将 \"套餐标记\" 的值修改为\"组合\", 仅当条件满足时, 否则为\"非组合\"。\n",
    "Calc_Pack_Mark = Calc_Pack_Mark.withColumn(\"组合标记(简)\", when(col(\"OLAP订单号计数\") >= 2, lit(\"组合\")).otherwise(lit(\"非组合\")))\n",
    "# --------------------------------------------------\n",
    "Calc_Pack_Mark = Calc_Pack_Mark.withColumn(\"组合标记(细)\", when(col(\"OLAP订单号计数\") >= 2, format_string(\"%s(%d)\", lit(\"组合\"), col(\"订单号\")))\n",
    "                                                          .otherwise(format_string(\"%s(%d)\", lit(\"非组合\"), col(\"订单号\"))))\n",
    "# --------------------------------------------------\n",
    "Calc_Pack_Mark.select([\"订单号\", \"日期\", \"产品名称\", \"考核毛利\", \"OLAP订单号计数\", \"组合标记(简)\", \"组合标记(细)\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "879f61b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+----------------+--------+------------+\n",
      "|         导购|    组合标记(细)|  订单号|OLAP组合毛利|\n",
      "+-------------+----------------+--------+------------+\n",
      "|杨能能(6732X)|  组合(22379253)|22379253|      281.12|\n",
      "|  梁宇(95919)|非组合(22381891)|22381891|         0.0|\n",
      "|费明凯(21411)|非组合(22519104)|22519104|         0.0|\n",
      "|周蓓丽(40049)|  组合(22532403)|22532403|      331.34|\n",
      "|  吴鸿(40868)|  组合(22666488)|22666488|     1319.62|\n",
      "|王亚雷(8417X)|非组合(22710804)|22710804|      269.53|\n",
      "|蒋桂华(84040)|非组合(22722720)|22722720|         0.0|\n",
      "|  邵伟(62194)|非组合(22889072)|22889072|       211.2|\n",
      "|王建树(76414)|非组合(22993158)|22993158|         0.0|\n",
      "|     系统结转|  组合(22374830)|22374830|     -415.21|\n",
      "|张敬攀(05053)|  组合(22377642)|22377642|      709.57|\n",
      "|万春花(27686)|  组合(22690419)|22690419|      1489.0|\n",
      "|         李燕|非组合(22813387)|22813387|       682.0|\n",
      "|  周月(30443)|  组合(23056266)|23056266|     2329.44|\n",
      "|童贤娟(89869)|非组合(22358937)|22358937|       77.48|\n",
      "|  周方(53010)|非组合(22393520)|22393520|       130.0|\n",
      "|姜双苗(20849)|  组合(22434983)|22434983|      344.54|\n",
      "|  韩航(01755)|  组合(22449659)|22449659|      423.49|\n",
      "|卿亮亮(64810)|非组合(22646705)|22646705|       108.0|\n",
      "|  蹇微(44469)|非组合(22686939)|22686939|       769.0|\n",
      "+-------------+----------------+--------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: 聚合组合毛利\n",
    "Agg_Pack_Prof = Calc_Pack_Mark\n",
    "# --------------------------------------------------\n",
    "Agg_Pack_Prof = Agg_Pack_Prof.select([\"订单号\", \"导购\", \"组合标记(细)\", \"考核毛利\"]).groupBy([\"导购\", \"组合标记(细)\"]).agg({\"订单号\":\"max\", \"考核毛利\":\"sum\"})\n",
    "# --------------------------------------------------\n",
    "Agg_Pack_Prof = Agg_Pack_Prof.withColumnRenamed(\"max(订单号)\",   \"订单号\")\n",
    "# --------------------------------------------------\n",
    "Agg_Pack_Prof = Agg_Pack_Prof.withColumnRenamed(\"sum(考核毛利)\", \"OLAP组合毛利\")\n",
    "# --------------------------------------------------\n",
    "Agg_Pack_Prof = Agg_Pack_Prof.withColumn(\"OLAP组合毛利\", pyspark.sql.functions.round(col(\"OLAP组合毛利\"), 2))\n",
    "# --------------------------------------------------\n",
    "Agg_Pack_Prof.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a5fcee16-e2a6-4d01-810a-7f1cf8b09e8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+----------------+--------+------------+--------------+\n",
      "|         导购|    组合标记(细)|  订单号|OLAP组合毛利|OLAP高毛利销退|\n",
      "+-------------+----------------+--------+------------+--------------+\n",
      "|杨能能(6732X)|  组合(22379253)|22379253|      281.12|             0|\n",
      "|  梁宇(95919)|非组合(22381891)|22381891|         0.0|             0|\n",
      "|费明凯(21411)|非组合(22519104)|22519104|         0.0|             0|\n",
      "|周蓓丽(40049)|  组合(22532403)|22532403|      331.34|             0|\n",
      "|  吴鸿(40868)|  组合(22666488)|22666488|     1319.62|             0|\n",
      "|王亚雷(8417X)|非组合(22710804)|22710804|      269.53|             0|\n",
      "|蒋桂华(84040)|非组合(22722720)|22722720|         0.0|             0|\n",
      "|  邵伟(62194)|非组合(22889072)|22889072|       211.2|             0|\n",
      "|王建树(76414)|非组合(22993158)|22993158|         0.0|             0|\n",
      "|     系统结转|  组合(22374830)|22374830|     -415.21|             0|\n",
      "|张敬攀(05053)|  组合(22377642)|22377642|      709.57|             0|\n",
      "|万春花(27686)|  组合(22690419)|22690419|      1489.0|             0|\n",
      "|         李燕|非组合(22813387)|22813387|       682.0|             0|\n",
      "|  周月(30443)|  组合(23056266)|23056266|     2329.44|             0|\n",
      "|童贤娟(89869)|非组合(22358937)|22358937|       77.48|             0|\n",
      "|  周方(53010)|非组合(22393520)|22393520|       130.0|             0|\n",
      "|姜双苗(20849)|  组合(22434983)|22434983|      344.54|             0|\n",
      "|  韩航(01755)|  组合(22449659)|22449659|      423.49|             0|\n",
      "|卿亮亮(64810)|非组合(22646705)|22646705|       108.0|             0|\n",
      "|  蹇微(44469)|非组合(22686939)|22686939|       769.0|             0|\n",
      "+-------------+----------------+--------+------------+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: 高毛利销退\n",
    "# --------------------------------------------------\n",
    "High_Prof_Sell_Ret = Agg_Pack_Prof\n",
    "# --------------------------------------------------\n",
    "High_Prof_Sell_Ret = High_Prof_Sell_Ret.withColumn(\"OLAP高毛利销退\", when(col(\"OLAP组合毛利\") >=  5000,  1)\n",
    "                                                                    .when(col(\"OLAP组合毛利\") <= -5000, -1)\n",
    "                                                                    .otherwise(0))\n",
    "# --------------------------------------------------\n",
    "High_Prof_Sell_Ret.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "86aaa4d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+--------------+\n",
      "|         导购|OLAP高毛利计数|\n",
      "+-------------+--------------+\n",
      "|  孙萍(70027)|            32|\n",
      "|谯懿飞(32116)|            28|\n",
      "|  彭星(83644)|            26|\n",
      "|周川越(23797)|            26|\n",
      "|吴淑婉(26041)|            25|\n",
      "|陈小英(14426)|            24|\n",
      "|  杨明(2427X)|            22|\n",
      "|卿亮亮(64810)|            21|\n",
      "|  邓旭(50418)|            21|\n",
      "|  唐杨(81439)|            21|\n",
      "|熊晓姣(56845)|            20|\n",
      "|王坤雨(0130X)|            20|\n",
      "|  毛婷(98406)|            20|\n",
      "|张富桂(37014)|            19|\n",
      "|何静瑶(72023)|            19|\n",
      "|尹政凯(16918)|            19|\n",
      "|彭林圆(11162)|            18|\n",
      "|苟林林(10025)|            18|\n",
      "|周椿斐(63778)|            18|\n",
      "|  唐珊(8362X)|            18|\n",
      "+-------------+--------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 预览数据: 高组合毛利计数\n",
    "# --------------------------------------------------\n",
    "High_Prof_Pack = High_Prof_Sell_Ret\n",
    "# --------------------------------------------------\n",
    "High_Prof_Pack = High_Prof_Pack.filter(~col(\"组合标记(细)\").contains(\"非\"))\n",
    "# --------------------------------------------------\n",
    "High_Prof_Pack = High_Prof_Pack.select([\"导购\", \"OLAP高毛利销退\"]).groupBy(\"导购\").sum(\"OLAP高毛利销退\")\n",
    "# --------------------------------------------------\n",
    "High_Prof_Pack = High_Prof_Pack.withColumnRenamed(\"sum(OLAP高毛利销退)\", \"OLAP高毛利计数\")\n",
    "# --------------------------------------------------\n",
    "High_Prof_Pack.sort(\"OLAP高毛利计数\", ascending=False).show()\n",
    "#High_Prof_Pack.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "66f319fa-7642-461b-b712-10363e228050",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------+----------------+--------+------------+--------------+----------------+\n",
      "|         导购|    组合标记(细)|  订单号|OLAP组合毛利|OLAP高毛利销退|OLAP组合毛利取整|\n",
      "+-------------+----------------+--------+------------+--------------+----------------+\n",
      "|杨能能(6732X)|  组合(22379253)|22379253|      281.12|             0|             0.0|\n",
      "|  梁宇(95919)|非组合(22381891)|22381891|         0.0|             0|             0.0|\n",
      "|费明凯(21411)|非组合(22519104)|22519104|         0.0|             0|             0.0|\n",
      "|周蓓丽(40049)|  组合(22532403)|22532403|      331.34|             0|             0.0|\n",
      "|  吴鸿(40868)|  组合(22666488)|22666488|     1319.62|             0|          1000.0|\n",
      "|王亚雷(8417X)|非组合(22710804)|22710804|      269.53|             0|             0.0|\n",
      "|蒋桂华(84040)|非组合(22722720)|22722720|         0.0|             0|             0.0|\n",
      "|  邵伟(62194)|非组合(22889072)|22889072|       211.2|             0|             0.0|\n",
      "|王建树(76414)|非组合(22993158)|22993158|         0.0|             0|             0.0|\n",
      "|     系统结转|  组合(22374830)|22374830|     -415.21|             0|             0.0|\n",
      "|张敬攀(05053)|  组合(22377642)|22377642|      709.57|             0|           500.0|\n",
      "|万春花(27686)|  组合(22690419)|22690419|      1489.0|             0|          1000.0|\n",
      "|         李燕|非组合(22813387)|22813387|       682.0|             0|           500.0|\n",
      "|  周月(30443)|  组合(23056266)|23056266|     2329.44|             0|          2000.0|\n",
      "|童贤娟(89869)|非组合(22358937)|22358937|       77.48|             0|             0.0|\n",
      "|  周方(53010)|非组合(22393520)|22393520|       130.0|             0|             0.0|\n",
      "|姜双苗(20849)|  组合(22434983)|22434983|      344.54|             0|             0.0|\n",
      "|  韩航(01755)|  组合(22449659)|22449659|      423.49|             0|             0.0|\n",
      "|卿亮亮(64810)|非组合(22646705)|22646705|       108.0|             0|             0.0|\n",
      "|  蹇微(44469)|非组合(22686939)|22686939|       769.0|             0|           500.0|\n",
      "+-------------+----------------+--------+------------+--------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 计算字段: OLAP组合毛利取整\n",
    "Pack_Prof_Round_Down = High_Prof_Sell_Ret\n",
    "# --------------------------------------------------\n",
    "Pack_Prof_Round_Down = Pack_Prof_Round_Down.withColumn(\"OLAP组合毛利取整\", col(\"OLAP组合毛利\") - (col(\"OLAP组合毛利\") % 500))\n",
    "# --------------------------------------------------\n",
    "Pack_Prof_Round_Down.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "dffb2549-c345-4d47-bf18-fdab2a8339ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+----+----+------------------------------+----+----+----+--------+-------------+----------------+------------+--------------+----------------+\n",
      "|  订单号|      日期|体系|部门|                      产品名称|单价|数量|金额|考核毛利|         导购|    组合标记(细)|OLAP组合毛利|OLAP高毛利销退|OLAP组合毛利取整|\n",
      "+--------+----------+----+----+------------------------------+----+----+----+--------+-------------+----------------+------------+--------------+----------------+\n",
      "|22898966|2023-10-07|形象|西安|                      技术服务|   0|   1| 100|   100.0|  丁毅(8163X)|非组合(22898966)|       100.0|             0|             0.0|\n",
      "|22911239|2023-10-08|形象|贵州|                      保障服务|   0|   0|1470|  1470.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|          苹果iphone15(256G)绿|   0|   1|6699|   36.22|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|   Nuguma真无线蓝牙耳机AX10/白|   0|   1| 438|  401.15|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|                      验机服务|   0|   1| 491|   491.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|                   两年碎屏PRO|   0|   1| 228|    38.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|       TredPro镜头膜iP15/Pl...|   0|   1| 129|   120.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|    TredPro膜iP15康宁全屏防...|   0|   1| 190|   157.7|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|          Mophie PD 30W USB...|   0|   1| 168|   125.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|    RideAir苹果手机壳iP15斜...|   0|   1| 190|   164.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|RideAir透明磁吸车载无线充支...|   0|   1| 268|   222.8|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22911239|2023-10-08|形象|贵州|                达尔文保障计划|   0|   1| 378|    63.0|丁茂迪(42188)|  组合(22911239)|     3288.87|             0|          3000.0|\n",
      "|22393091|2023-08-08|形象|重庆|          USB-C to Lightnin...|   0|   1| 169|   143.0|万佳惠(25022)|  组合(22393091)|       514.8|             0|           500.0|\n",
      "|22393091|2023-08-08|形象|重庆|          Mophie PD 30W USB...|   0|   1| 298|   253.8|万佳惠(25022)|  组合(22393091)|       514.8|             0|           500.0|\n",
      "|22393091|2023-08-08|形象|重庆|                    活动优惠券|   0|   0| -70|   -70.0|万佳惠(25022)|  组合(22393091)|       514.8|             0|           500.0|\n",
      "|22393091|2023-08-08|形象|重庆|    iPharos膜iP11全画幅水晶...|   0|   1| 198|   188.0|万佳惠(25022)|  组合(22393091)|       514.8|             0|           500.0|\n",
      "|22409395|2023-08-11|形象|重庆|         苹果手机壳硅胶挂绳/紫|   0|   1|  68|   67.47|万佳惠(25022)|  组合(22409395)|     4376.19|             0|          4000.0|\n",
      "|22409395|2023-08-11|形象|重庆|     RideAir苹果手机壳iP14P...|   0|   1| 240|   209.2|万佳惠(25022)|  组合(22409395)|     4376.19|             0|          4000.0|\n",
      "|22409395|2023-08-11|形象|重庆|       RideAir膜iP14Pro康宁...|   0|   1| 240|   208.0|万佳惠(25022)|  组合(22409395)|     4376.19|             0|          4000.0|\n",
      "|22409395|2023-08-11|形象|重庆|                达尔文保障计划|   0|   1| 378|    63.0|万佳惠(25022)|  组合(22409395)|     4376.19|             0|          4000.0|\n",
      "+--------+----------+----+----+------------------------------+----+----+----+--------+-------------+----------------+------------+--------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 合并数据\n",
    "# --------------------------------------------------\n",
    "Joined = Calc_Pack_Mark.join(High_Prof_Sell_Ret.select([\"导购\", \"组合标记(细)\", \"OLAP组合毛利\", \"OLAP高毛利销退\"]), on=[\"导购\", \"组合标记(细)\"], how=\"left\")\n",
    "# --------------------------------------------------\n",
    "Joined.cache()\n",
    "# --------------------------------------------------\n",
    "Joined = Joined.join(Pack_Prof_Round_Down.select([\"导购\", \"组合标记(细)\", \"OLAP组合毛利取整\"]), on=[\"导购\", \"组合标记(细)\"], how=\"left\")\n",
    "# --------------------------------------------------\n",
    "Joined.select([Calc_Pack_Mark[\"订单号\"], \"日期\", \"体系\", \"部门\", \"产品名称\", \"单价\", \"数量\", \"金额\", \"考核毛利\", \"导购\", \"组合标记(细)\",\n",
    "               High_Prof_Sell_Ret[\"OLAP组合毛利\"], High_Prof_Sell_Ret[\"OLAP高毛利销退\"],\n",
    "               Pack_Prof_Round_Down[\"OLAP组合毛利取整\"]]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c23bb80c-7cf7-47f8-a97c-a1e5e52fe40c",
   "metadata": {},
   "outputs": [],
   "source": [
    "Joined.coalesce(1).write.mode('overwrite').option('header', 'true').csv(\"file:///C:\\\\GF-Datas\\\\Csv_From_SparkSQL_讯捷集团组合销售分析.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9b35957a-990f-46e5-bd35-12b442c4d4ea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1156476"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Joined.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dcdc1b5d-9b50-42eb-adad-6a65679d5716",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
